This article is about to manually save the record in Microsoft Access using VBA. A bound control is one that is bound to a field in a table or to a function. Bound controls are easy to identify as they will contain the field names from the table in design view. Bound elements are linked directly to the tables, and when you change any data within forms, your changes are immediately written to the tables. This can often lead to user questioning why "when I close a form does it save the changes?" This is because this is bound behavior of form with table. To prevent any updates you must use procedures such as Before Update to cancel if necessary. In this article we are stop the updating automatically.

To stop bound forms update automatically we have to create a table as shown in Fig 1.1.

Then we have to create a form using table by selecting Form wizard from the ribbon. This will enable the automatic update option. Now add button using enabling control wizard and in command button wizard select the save record option. Now open the Form in Form view. You will see if we make changes in the text-box fields the record will automatically save without any prior notification. Now for stopping automatic update we have to add one more unbound button as shown in Fig 1.2. And write code for stop automatic update as shown end if this article.

After completion of coding we have to test it. For this change the value from the text box and click on save record button. As you click on that a message will pop up as shown in Fig 1.3. This is the confirmation message. If you will close it the record not altered and if after press ok and then pressing on manual save button, then only the record will save.

You can check also the table the record will update as shown in Fig 1.4.

VBA Code

Option Compare Database
Option Explicit
Private btnflag As Boolean
Private Sub Command10_Click()
btnflag = True
Call DoCmd.RunCommand (acCmdSaveRecord)
btnflag = False
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MSSG As String
If Not btnflag Then
Cancel = True
MSSG = " Please use the Save button to update the changes," & "or Escape to reset them."
Call MsgBox(Prompt:=MSSG, Title:="Before_Update")
End If
End Sub